﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;

namespace Project_Udrinkwater.Empoyee_Management
{
    public partial class InvoiceBill : System.Web.UI.Page
    {

        private string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            
            SqlConnection objConn = new SqlConnection();
            SqlCommand objCmd = new SqlCommand();
            SqlDataAdapter dtAdapter = new SqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt = null;
            string strConnString = null;
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(@"SELECT TB_Invoice.Invoice_doc_NO,TB_Employee.FirstName +' '+ TB_Employee.LastName As Emp,TB_Order.Deadlines_Date,
                            TB_Order.Order_doc_NO,
                            TB_Order.Customer_ID,TB_Customer.Firstname + ' ' + TB_Customer.Lastname As Cus,TB_Order.DesAddres,
                            ROW_NUMBER() OVER (ORDER BY TB_Order_List.id ASC) AS RowID,TB_Product.Product_Name ,TB_Order_List.Price_Unit,
                            TB_Order_List.Amount,TB_Order_List.Discount
                            from TB_Invoice
                            Inner join TB_Order ON TB_Invoice.Order_ID =  TB_Order.Order_ID
                            inner join TB_Customer ON TB_Order.Customer_ID = TB_Customer.Customer_ID
                            Inner join TB_Employee ON TB_Invoice.Employee_ID = TB_Employee.Employee_ID
                            LEFT JOIN TB_Order_List ON TB_Order.Order_ID = TB_Order_List.Order_ID
                            LEFT JOIN TB_Order_Category ON TB_Order_List.Order_Category_ID = TB_Order_Category.Order_Category_ID
                            LEFT JOIN TB_Product ON TB_Order_List.Product_ID = TB_Product.Product_ID AND TB_Order_Category.Product_ID=TB_Product.Product_ID");
            strSQL.Append(" WHERE TB_Invoice.Invoice_ID = "+Session["Invoice_ID"].ToString()+" ");
            
            objConn.ConnectionString = Connection;
            var _with1 = objCmd;
            _with1.Connection = objConn;
            _with1.CommandText = strSQL.ToString();
            _with1.CommandType = CommandType.Text;
            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds, "CrystalReport2");
            dt = ds.Tables[0];
           
            dtAdapter = null;
            objConn.Close();
            objConn = null;

            ReportDocument rpt = new ReportDocument();
            rpt.Load(Server.MapPath("~/Empoyee_Management/CrystalReport2.rpt"));
            rpt.SetDataSource(dt);
            rpt.SetParameterValue("Invoice_doc_NO", dt.Rows[0]["Invoice_doc_NO"].ToString());
            rpt.SetParameterValue("Emp", dt.Rows[0]["Emp"].ToString());
            rpt.SetParameterValue("Deadlines_Date", dt.Rows[0]["Deadlines_Date"].ToString());
            rpt.SetParameterValue("Order_doc_NO", dt.Rows[0]["Order_doc_NO"].ToString());
            rpt.SetParameterValue("DesAddres", dt.Rows[0]["DesAddres"].ToString());
            rpt.SetParameterValue("Customer_ID", dt.Rows[0]["Customer_ID"].ToString());
            rpt.SetParameterValue("Cus", dt.Rows[0]["Cus"].ToString());
            this.CrystalReportViewer3.ReportSource = rpt;
        }
    }
}